package com.example.menuvirtualclient.repository;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.net.nsd.NsdManager.DiscoveryListener;
import android.util.Log;

import com.example.menuvirtualclient.core.*;
import com.example.menuvirtualclient.data.Repository;
import com.example.menuvirtualclient.util.Common;

public class DishRepository extends Repository<Dish> {

	private static final String LOGTAG = "DishRepository";
	
	public DishRepository(Context context) {
		super(context);
	}
	
	private int getToday(){
		return Calendar.getInstance().get(Calendar.DAY_OF_WEEK);
	}
	
	private String getHour(){
		return Calendar.getInstance().get(Calendar.HOUR_OF_DAY) + "00";
	}
	
	
	public void deleteAll() {
		SQLiteDatabase db = null;
		try{
		
			
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			db.delete("Dishes", null, null);
			
		}
		catch(SQLiteException ex){
			Log.e(LOGTAG, ex.getMessage());
			throw ex;
		}		
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
		}
	}
	
	public List<Dish> GetDishesByMenu(int menuId) {
		SQLiteDatabase db = null;
		try{
			List<Dish> list = new ArrayList<Dish>();
			String[] filter = new String[1];
			filter[0] = Long.toString(menuId);
			
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			Cursor cursor = db.rawQuery("SELECT d.id, d.name, d.description, d.photo, d.status, d.celiac, d.cookingpoint, d.type, 0 day, 0 hour_from, 0 hour_to, 0 value, 0 size " +
					 "FROM Dishes_Menues dm INNER JOIN Dishes d ON d.id = dm.dishid " +
					" INNER JOIN Prices p ON p.dish_id = d.id WHERE dm.menuid = "+menuId+"  AND p.day="+getToday()+
					" AND p.hour_from<="+getHour()+" AND p.hour_to>="+getHour() + " GROUP BY d.id, d.name, d.description, d.photo, d.status, " +
							"d.celiac, d.cookingpoint, d.type,  day,  hour_from,  hour_to,  value,  size", null);
			
			cursor.moveToFirst();
		    while (!cursor.isAfterLast()) {
		    	list.add(BuildNewEntity(cursor));
		    	cursor.moveToNext();
		    }
		    cursor.close();
		    db.close();
		    return list;
		}
		catch(SQLiteException ex){
			
			Log.e(LOGTAG, ex.getMessage());
			throw ex;
		}		
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
		}
	}
	
	@Override
	public Dish Get(long id) {
		SQLiteDatabase db = null;
		Dish entity = null;
		try{
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();			
			Cursor cursor = db.rawQuery("SELECT d.id, d.name, d.description, d.photo, d.status, d.celiac, d.cookingpoint, d.type, 0 day, 0 hour_from, 0 hour_to, 0 value, 0 size " +
					" FROM Dishes d INNER JOIN Prices p ON p.dish_id = d.id " +
					" WHERE d.id = "+id+"  AND p.day = "+getToday()+
					" AND p.hour_from<="+getHour()+" AND p.hour_to>="+getHour() + " GROUP BY d.id, d.name, d.description, d.photo, d.status, " +
							"d.celiac, d.cookingpoint, d.type, day,  hour_from,  hour_to,  value,  size", null);
			
			cursor.moveToFirst();
			entity = BuildNewEntity(cursor); 
			cursor.close();
		    db.close();
		}
		catch(Exception ex){
			Log.e(LOGTAG, ex.getMessage());			
		}
		finally{
			if(db != null)
				if(db.isOpen()) {
					db.close();
					Log.e(LOGTAG, "Close database");		
				}
		}
		return entity;
	}
	
	@Override
	public void Insert(Dish entity) {
		SQLiteDatabase db = null;
		try{
			ContentValues newRow = new ContentValues();
			newRow.put("id", entity.getId());			
			newRow.put("name", entity.getName());
			newRow.put("description", entity.getDescription());
			newRow.put("photo", entity.getPhoto());
			newRow.put("status", entity.getStatus());
			newRow.put("celiac", entity.getCeliac());
			newRow.put("cookingpoint", entity.getCookingPoint());
			newRow.put("type", entity.getType());

			
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			db.insert(getTableName(), null, newRow);
			
			db.close();
		}
		catch(NullPointerException ex){
			Log.e(LOGTAG, ex.getMessage());
		}
		catch(SQLiteException ex){
			Log.e(LOGTAG, ex.getMessage());
		}
		catch(Exception ex){
			Log.e(LOGTAG, ex.getMessage());		
		}
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
			_session.Close();
		}
	}
	
	public List<Comment> GetCommnetsByDish(int dishId) {
		SQLiteDatabase db = null;
		try{
			List<Comment> list = new ArrayList<Comment>();
			String[] filter = new String[1];
			filter[0] = Long.toString(dishId);
			
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			Cursor cursor = db.rawQuery("SELECT c.id, c.description, c.price FROM Dishes_Comments dc INNER JOIN Comments c ON c.id = dc.commentid WHERE dc.dishid = ? ", filter);
			
			cursor.moveToFirst();
		    while (!cursor.isAfterLast()) {
		    	Comment comment = new Comment();
		    	comment.setId(cursor.getInt(0));
		    	comment.setDescription(cursor.getString(1));
		    	double price = Common.Round(cursor.getDouble(2), 2); 
				comment.setPrice(price);
		    	
		    	list.add(comment);
		    	cursor.moveToNext();
		    }
		    cursor.close();
		    db.close();
		    return list;
		}
		catch(SQLiteException ex){
			Log.e(LOGTAG, ex.getMessage());
			throw ex;
		}		
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
		}
	}
	
	
	public void saveDishComment(DishComment entity) {
		SQLiteDatabase db = null;
		try{
			ContentValues newRow = new ContentValues();
			newRow.put("dishid", entity.getDishId());			
			newRow.put("commentid", entity.getCommentId());			
			
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			db.insert("Dishes_Comments", null, newRow);
			
			db.close();
		}		
		catch(SQLiteException ex){
			Log.e(LOGTAG, ex.getMessage());
		}
		catch(Exception ex){
			Log.e(LOGTAG, ex.getMessage());		
		}
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
			_session.Close();
		}
	}
	
	public List<Price> getPriceByDish(int dishId) {
		SQLiteDatabase db = null;
		try{
			List<Price> list = new ArrayList<Price>();

			
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			Cursor cursor = db.rawQuery("SELECT dish_id, size, value " +
					" FROM Prices WHERE dish_id = "+dishId+"  AND day = "+getToday()+
					" AND CAST(hour_from as integer)<="+getHour()+" AND CAST(hour_to as integer)>="+getHour(), null);
			cursor.moveToFirst();
		    while (!cursor.isAfterLast()) {
		    	Price price = new Price();
		    	price.setDish_id(cursor.getInt(0));
		    	price.setSize(cursor.getInt(1));
		    	double value = Common.Round(cursor.getDouble(2), 2); 
		    	price.setValue(value);
		    	
		    	list.add(price);
		    	cursor.moveToNext();
		    }
		    cursor.close();
		    db.close();
		    return list;
		}
		catch(SQLiteException ex){
			Log.e(LOGTAG, ex.getMessage());
			throw ex;
		}		
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
		}
	}
	
	public void savePrice(Price entity) {
		SQLiteDatabase db = null;
		try{
			ContentValues newRow = new ContentValues();
			newRow.put("id", entity.getId());			
			newRow.put("day", entity.getDay());	
			newRow.put("hour_from", entity.getHour_from());	
			newRow.put("hour_to", entity.getHour_to());	
			newRow.put("value", entity.getValue());	
			newRow.put("dish_id", entity.getDish_id());	
			newRow.put("size", entity.getSize());	
			
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			db.insert("Prices", null, newRow);
			Log.e(LOGTAG, "insert prices");
			
			db.close();
		}		
		catch(SQLiteException ex){
			Log.e(LOGTAG, ex.getMessage());
		}
		catch(Exception ex){
			Log.e(LOGTAG, ex.getMessage());		
		}
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
			_session.Close();
		}
	}
	
	public void deleteAllPrices() {
		SQLiteDatabase db = null;
		try{
			
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			db.delete("Prices", null, null);
			
			db.close();
		}		
		catch(SQLiteException ex){
			Log.e(LOGTAG, ex.getMessage());
		}
		catch(Exception ex){
			Log.e(LOGTAG, ex.getMessage());		
		}
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
			_session.Close();
		}
	}
	
	public boolean exist(DishComment entity) {
		SQLiteDatabase db = null;
		try{
			List<DishComment> list = new ArrayList<DishComment>();
			String[] filter = new String[2];
			filter[0] = Integer.toString(entity.getDishId());
			filter[1] = Integer.toString(entity.getCommentId());
			if(!_session.IsOpen())
				_session.Open();
			
			db = _session.Connection();
			Cursor cursor = db.rawQuery("SELECT dishid, commentid FROM Dishes_Comments WHERE dishid = ? AND commentid = ? ", filter);
			
			cursor.moveToFirst();
		    while (!cursor.isAfterLast()) {
		    	DishComment dishComment = new DishComment();
		    	dishComment.setDishId(cursor.getInt(0));
		    	dishComment.setCommentId(cursor.getInt(1));
		    	
		    	list.add(dishComment);
		    	cursor.moveToNext();
		    }
		    cursor.close();
		    db.close();
		    if(list != null && list.size() > 0)
		    	return true;
		    else
		    	return false;
		}
		catch(SQLiteException ex){
			Log.e(LOGTAG, ex.getMessage());
			throw ex;
		}		
		finally{
			if(db != null)
				if(db.isOpen()) db.close();
		}
	}
	
	@Override
	public String getTableName() {
		return "Dishes";
	}
	
	@Override
	public Dish BuildNewEntity(Cursor cursor) {
		Dish dish = new Dish();
		dish.setId(cursor.getInt(0));
		dish.setName(cursor.getString(1));
		dish.setDescription(cursor.getString(2));
		dish.setPhoto(cursor.getString(3));
		dish.setStatus(cursor.getInt(4));
		dish.setCeliac(cursor.getInt(5));
		dish.setCookingPoint(cursor.getInt(6));
		dish.setType(cursor.getInt(7));

		return dish;
	}
}
